//package ws.performancetest.summary

import java.sql.*
import com.microsoft.sqlserver.jdbc.SQLServerDriver

/**
 *
 * @author Wilson Santos, <wilson.santos@ua.pt>
 */
public class BenchmarkResults {
  def static url = "jdbc:sqlserver://goldberg-tm2:1433;username=sa;password=sg4765j;database=DBVaio"
  def static con
  def static threadList = []
  def static cycleList = []
  def static folder = "images/results" + File.separator
  def static drivertypes = [
    "Leitura", "Actualização", "Inserção", "Remoção"
  ]
  def static cachetypes = [
    "10 ", "20", "50", "75", "100"
  ]
  def static drivertests = [
    // Read
    ["JDBC", "CJDBC_I"],
    ["JDBC", "CJDBC_S"],
    ["JDBC", "WJDBC"],
    ["WJDBC", "CJDBC_S"],
    ["WJDBC", "CJDBC_I"]
  ]
  def static cachetests = [
    ["CJDBC_I", "CJDBC_SM"]
  ]

  def static times = [
    'ExecT', 'SetupT', 'Total'
  ] as String[]
  
  static {
    try {
      con = new SQLServerDriver().connect(url, null)
    } catch(SQLException ex) {
      println ex.getMessage()
      System.exit(1)
    }
  }

  public static void main(String[] args) {
    File dir = new File(folder)
    if(!dir.exists()) {
      dir.mkdir()
    }
    
    println "Creating JDBCDriverTest Summary..."
    testResults("jdbccmp", drivertests, drivertypes, 'JDBCDriverTest_Summary', 1)

    println "Creating CacheTest Summary..."
    testResults("cachecmp", cachetests, cachetypes, 'CacheTest_Summary', 1)
    testResults("cachecmp", [["CJDBC_I", "CJDBC_S"]], ["100"], 'CacheTest_Summary', 1, false)

    println "Creating JDBCDriverColumnDelayTest Summary..."
    testResults("delayc1", drivertests, drivertypes[0..1], 'JDBCDriverColumnDelayTest_Summary', 2)    
    //testResults("delayc100", drivertests, drivertypes[0..1], 'JDBCDriverColumnDelayTest_Summary', 2)    

    println "Creating JDBCDriverLineDelayTest Summary..."
    testResults("delayl8", drivertests, drivertypes[0..1], 'JDBCDriverLineDelayTest_Summary', 1) 
    //testResults("delayl800", drivertests, drivertypes[0..1], 'JDBCDriverLineDelayTest_Summary', 2) 

    con.close()
  }
  
  def static getName(first, second) {
    first + "/" + second
  }

  def static getFileName(prefix, op, first, second) {
    "${prefix}-${first}-${second}-${op}"
  }

  def static convchars(name) {
    name.replaceAll("ã", "a").replaceAll("ç", "c").replaceAll(" ", "")
  }

  def static createSummary(testTypes, summaryTable, testId) {
    String sql = """
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Summary]') AND type in (N'U'))
    DROP TABLE [dbo].[Summary]

    SELECT * 
    INTO Summary
    FROM ${summaryTable}
    WHERE TestId=${testId}    
    """
    testTypes.each {
      sql = sql.concat("""
      IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Summary_${convchars(it)}]') AND type in (N'U'))
      DROP TABLE [dbo].[Summary_${convchars(it)}]

      SELECT *
      INTO Summary_${convchars(it)}
      FROM Summary
      WHERE Type like '%${it}%'
      """)
    }
    Statement stmt = con.createStatement()
    stmt.executeUpdate(sql)
    stmt.close()
  }

  def static testResults(prefix, ratios, testTypes, summaryTable, testId, summary = true) {
    if(summary) {
      createSummary(testTypes, summaryTable, testId)
      getThreadList()
      getCycleList()
    }

    testTypes.each { type ->
      def convtype = convchars(type)
      ratios.each { it ->
        ratio(prefix, convtype, it[0], it[1])
        createGnu(prefix, convtype, it[0], it[1], times[0], 2)
        createGnu(prefix, convtype, it[0], it[1], times[1], 3)
        createGnu(prefix, convtype, it[0], it[1], times[2], 4)

        createTex(prefix, type, it[0], it[1], times[0], times[1], times[2])
      }
    }
  }

  def static ratio(prefix, type, first, second) {
    String tableName = "Summary_${type}"
    String name = getName(first, second)
    String sql = """\
    SELECT '${name}' as 'Type', t1.Cycle, t2.NumberOfThreads
    , CAST ( t1.exect AS float ) / CAST ( t2.exect AS float ) AS ExecT
    , CAST ( t1.SetupT AS float ) / CAST ( t2.SetupT AS float ) AS SetupT
    , CAST ( t1.Total AS float ) / CAST ( t2.Total AS float ) AS Total 
    FROM ${tableName} T1
    INNER JOIN ${tableName} T2 ON t1.numberofthreads=t2.numberofthreads AND t1.cycle=t2.cycle
    WHERE t1.type like '${first} %' AND t2.type LIKE '${second} %'
    ORDER by t1.cycle, t1.numberofthreads
    """
    File file = new File(folder + getFileName(prefix, type, first, second))

    file.write("#" + name)

    Statement stmt = con.createStatement()
    ResultSet rs = stmt.executeQuery(sql)
    def prevCycle = -1
    while(rs.next()) {
      def cycle = rs.getInt(2)
      def nthreads = rs.getInt(3)
      def exect = rs.getDouble(4)
      def setupt = rs.getDouble(5)
      def total = rs.getDouble(6)

      if(cycle != prevCycle) {
        prevCycle = cycle
        file.append("\n\n#$cycle\n")
      }
      file.append("${nthreads};${exect};${setupt};${total}\n")
    }
    rs.close()
    stmt.close()
  }
  
  def static createGnu(prefix, type, first, second, time, tIdx) {
    String filename = getFileName(prefix, type, first, second)
    File file = new File("${folder}${filename}-${time}.gnu")
    String content = """\
    set size ratio 1.1
    set datafile separator ";"
    set xtics (${xtics()})
    set grid
    set key below width 0.8
#    set key bmargin center horizontal Right noreverse enhanced autotitles
    set terminal post enh #color
    set out '${filename}-${time}.eps'
#    set terminal png size 550,700
#    set out '${filename}-${time}.png'

    set title '${getName(first, second)} (${time})'

    set xlabel 'quantidade de threads'
    
    plot "${filename}" index 0 u 1:${tIdx} w lp title "${cycleList.get(0)}" """
    cycleList.subList(1, cycleList.size()).eachWithIndex { it, idx ->
      content = content.concat(", \"\" index ${idx + 1} u 1:${tIdx} w lp lw 4 title \"${it}\"")
    }

//    content = content.concat("""\
//    
//    replot
//    """)

    file.write(content)
  }

  def static createTex(prefix, type, first, second, time1, time2, time3) {
    String filename = getFileName(prefix, convchars(type), first, second)
    File file = new File("${folder}${filename}.tex")
    String content = """\
\\begin{figure}[H]
  \\centering
  \\caption{\\small {\\bf ${type}} ${"\$".concat(first.concat("/").concat(second.concat("\$")))} }
  \\label{fig:${filename}}
  \\subfloat{\\includegraphics[angle=-90,width=0.333333333\\textwidth]{images/results/${filename}-${time1}}}
  \\subfloat{\\includegraphics[angle=-90,width=0.333333333\\textwidth]{images/results/${filename}-${time2}}}
  \\subfloat{\\includegraphics[angle=-90,width=0.333333333\\textwidth]{images/results/${filename}-${time3}}}
\\end{figure}
    """
    file.write(content, "utf-8")
  }

  def static xtics() {
    String ret = ""
    threadList.each {
      ret += "\"${it}\" ${it}, "
    }
    ret.substring(0, ret.length() - 2)
  }

  def static getThreadList() {
    threadList.clear()

    Statement stmt = con.createStatement()
    ResultSet rs = stmt.executeQuery("SELECT DISTINCT NumberOfThreads FROM Summary")

    while(rs.next()) {
      threadList.add(rs.getInt(1))
    }

    rs.close()
    stmt.close()
  }

  def static getCycleList() {
    cycleList.clear()

    Statement stmt = con.createStatement()
    ResultSet rs = stmt.executeQuery("SELECT DISTINCT Cycle FROM Summary")

    while(rs.next()) {
      cycleList.add(rs.getInt(1))
    }

    rs.close()
    stmt.close()
  }
}

